# -*- coding: utf-8 -*-
from datetime import datetime
from io import BytesIO
import urllib
import xlsxwriter
from flask import Blueprint, g, make_response
from sqlalchemy import alias, and_, case, desc, func
from common.response import success, error
from common.error_code import ErrorCode
from dao.class_dao import ClassSubjectTeacher
from dao.group_dao import StudyGroup
from dao.point_dao import PointsReason, PointsRecord
from filter.auth import auth_required
from filter.decorators import validate_request, require_role
from dao.db import db
from dao.user_dao import Student, Class, Teacher, User
from model.request import PointsRankingRequest, PointsReportRequest, PointsStatisticsRequest, QueryPointsRecordsRequest, UpdateStudentPointsRequest

# 创建蓝图，定义接口路径前缀为 /api/point
point_bp = Blueprint('point', __name__, url_prefix='/api/point')

@point_bp.route('/update', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(UpdateStudentPointsRequest)
def update_points():
    """更新学生行为规范积分（加分或扣分）"""
    data = g.request_data
    
    # 1. 验证管理员/教师身份和口令
    current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
    if not current_teacher:
        return error(ErrorCode.FORBIDDEN, "当前用户不是教师，无法验证口令")
    if current_teacher.password_hash != data.admin_password:
        return error(ErrorCode.INVALID_PARAM, "管理员/教师口令错误")
    
    # 获取教师信息（管理员也可能有教师身份）
    teacher = Teacher.query.filter_by(user_id=g.user_id).first()
    if not teacher and g.role != 'admin':
        return error(ErrorCode.FORBIDDEN, "非教师用户无权操作")
    
    # 2. 验证学生是否存在
    student = Student.query.get(data.student_id)
    if not student:
        return error(ErrorCode.NOT_FOUND, "学生不存在")
    
    # 3. 教师权限检查（管理员跳过）
    if g.role == 'teacher' and teacher:
        # 检查学生所在班级是否为教师负责
        student_class = Class.query.get(student.class_id)
        if not student_class:
            return error(ErrorCode.NOT_FOUND, "学生所在班级不存在")
        
        # 检查是否为班主任
        is_head_teacher = (student_class.head_teacher_id == teacher.id)
        
        # 检查是否为任课老师
        is_subject_teacher = ClassSubjectTeacher.query.filter_by(
            class_id=student_class.id,
            teacher_id=teacher.id
        ).first() is not None
        
        if not is_head_teacher and not is_subject_teacher:
            return error(ErrorCode.FORBIDDEN, "你不负责该学生所在班级，无权操作其积分")
    
    # 记录原始积分用于返回
    original_points = student.performance_points
    
    # 更新学生积分
    student.performance_points += data.points_change
    
    # 4. 创建积分变动记录（使用调整后的表结构，只保留一个reason字段）
    record = PointsRecord(
        student_id=data.student_id,
        teacher_id=teacher.id if teacher else None,  # 管理员操作时可能为None
        points_change=data.points_change,
        reason=data.reason,  # 直接使用传入的理由
        created_at=datetime.now()
    )
    db.session.add(record)
    
    # 提交事务
    db.session.commit()
    
    # 5. 准备返回结果
    return success({
        "student_id": student.id,
        "student_name": student.user.name,
        "original_points": original_points,
        "points_change": data.points_change,
        "current_points": student.performance_points,
        "record_id": record.id,
        "reason": record.reason,
        "operated_by": current_teacher.id,
        "operated_at": record.created_at.strftime("%Y-%m-%d %H:%M:%S")
    })

@point_bp.route('/ranking', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(PointsRankingRequest)
def get_ranking():
    """获取指定班级学生的积分排名（处理并列情况）"""
    params = g.request_data
    
    # 1. 验证班级是否存在
    cls = Class.query.get(params.class_id)
    if not cls:
        return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 不存在")
    
    # 2. 验证当前用户（教师/管理员）身份
    current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
    if not current_teacher:
        return error(ErrorCode.FORBIDDEN, "当前用户无教师表记录，无权查看排名")
    
    # 3. 教师权限检查（管理员无需检查）
    if g.role == 'teacher':
        # 检查是否为该班级的班主任或任课老师
        is_head_teacher = (cls.head_teacher_id == current_teacher.id)
        is_subject_teacher = ClassSubjectTeacher.query.filter_by(
            class_id=cls.id,
            teacher_id=current_teacher.id
        ).first() is not None
        
        if not is_head_teacher and not is_subject_teacher:
            return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权查看积分排名")
    
    # 4. 查询该班级所有学生并按积分降序排序
    students = Student.query.filter_by(class_id=params.class_id)\
                            .order_by(desc(Student.performance_points))\
                            .all()
    
    if not students:
        return success({
            "class_id": cls.id,
            "class_name": cls.name,
            "ranking_list": [],
            "total_students": 0
        })
    
    # 5. 提取所有分数用于排名计算
    all_scores = [s.performance_points for s in students]
    
    # 6. 准备返回结果（使用辅助函数计算排名）
    result = []
    for student in students:
        student_name = User.query.get(student.user_id).name if student.user_id else "未知"
        rank = calculate_ranking(all_scores, student.performance_points)
        
        result.append({
            "user_id": student.user_id,
            "student_id": student.id,
            "student_name": student_name,
            "student_number": student.student_id,
            "performance_points": student.performance_points,
            "rank": rank
        })
    
    return success({
        "class_id": cls.id,
        "class_name": cls.name,
        "ranking_list": result,
        "total_students": len(result)
    })

@point_bp.route('/group-ranking', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(PointsRankingRequest)
def get_group_ranking():
    """获取指定班级的小组积分排名（处理并列情况）"""
    params = g.request_data
    
    # 1. 验证班级是否存在
    cls = Class.query.get(params.class_id)
    if not cls:
        return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 不存在")
    
    # 2. 验证教师权限（管理员跳过）
    if g.role == 'teacher':
        current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not current_teacher:
            return error(ErrorCode.FORBIDDEN, "当前用户无教师表记录，无权操作")
        
        # 检查是否为班主任或任课老师
        is_head_teacher = (cls.head_teacher_id == current_teacher.id)
        is_subject_teacher = ClassSubjectTeacher.query.filter_by(
            class_id=cls.id,
            teacher_id=current_teacher.id
        ).first() is not None
        
        if not is_head_teacher and not is_subject_teacher:
            return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权查看")
    
    # 3. 获取班级内所有小组并计算积分
    groups = StudyGroup.query.filter_by(class_id=params.class_id).all()
    if not groups:
        return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 没有小组信息")
    
    # 4. 计算每个小组的总积分
    group_rankings = []
    for group in groups:
        group_students = Student.query.filter_by(
            class_id=params.class_id,
            group_id=group.id
        ).all()
        
        total_points = sum(s.performance_points for s in group_students)
        
        # 获取组长信息（假设StudyGroup表有leader_id字段关联到students表）
        leader_info = None
        if group.leader_id:  # 假设小组表中有leader_id字段存储组长的student_id
            leader = Student.query.filter_by(
                id=group.leader_id,
                group_id=group.id  # 确保组长属于当前小组
            ).first()
            
            if leader:
                # 获取组长的用户信息
                leader_user = User.query.get(leader.user_id)
                leader_info = {
                    "leader_student_id": leader.id,  # 组长在students表的ID
                    "leader_user_id": leader.user_id,  # 组长在users表的ID
                    "leader_name": leader_user.name if leader_user else None  # 组长姓名
                }
        
        group_rankings.append({
            "group_id": group.id,
            "group_name": group.name,
            "total_points": total_points,
            "leader_info": leader_info  # 组长信息，无组长则为None
        })
    
    # 5. 提取所有小组积分用于排名计算
    all_totals = sorted([g["total_points"] for g in group_rankings], reverse=True)
    
    # 6. 计算排名并按积分降序排序
    for group in group_rankings:
        group["rank"] = calculate_ranking(all_totals, group["total_points"])
    
    # 按积分降序排列（保持排名一致性）
    group_rankings.sort(key=lambda x: x["total_points"], reverse=True)
    
    return success(group_rankings)

# 通用排名计算辅助函数（处理并列情况）
def calculate_ranking(all_values, target_value):
    """
    计算目标值在所有值中的排名（处理并列情况）
    :param all_values: 所有值的列表（已按降序排列）
    :param target_value: 要计算排名的目标值
    :return: 排名（整数）
    """
    if not all_values:
        return 0
    
    rank = 1
    for i in range(len(all_values)):
        if i > 0 and all_values[i] != all_values[i-1]:
            rank = i + 1
        if all_values[i] == target_value:
            return rank
    return len(all_values)  # 未找到时返回最后一名

@point_bp.route('/student-self', methods=['POST'])
@auth_required
@require_role(['student'])
def get_student_self_points():
    """学生查询自己的积分、排名及所在小组的积分和排名"""
    # 1. 获取当前学生信息
    current_student = Student.query.filter_by(user_id=g.user_id).first()
    if not current_student:
        return error(ErrorCode.NOT_FOUND, "学生信息不存在")
    
    # 2. 获取学生所在班级和小组信息
    student_class = Class.query.get(current_student.class_id)
    if not student_class:
        return error(ErrorCode.NOT_FOUND, "学生所在班级不存在")
    
    student_group = StudyGroup.query.get(current_student.group_id) if current_student.group_id else None
    
    # 3. 获取学生个人积分
    student_points = current_student.performance_points
    
    # 4. 计算学生个人在班级中的排名
    class_rank = calculate_student_class_rank(current_student.class_id, student_points)
    
    # 5. 计算所在小组的积分和排名（如果学生已分配小组）
    group_info = None
    if student_group and current_student.group_id:
        # 计算小组总积分
        group_total = calculate_group_total_points(
            current_student.class_id, 
            current_student.group_id
        )
        
        # 计算小组在班级中的排名
        group_rank = calculate_group_class_rank(current_student.class_id, group_total)
        
        group_info = {
            "group_id": student_group.id,
            "group_name": student_group.name,
            "total_points": group_total,
            "group_rank": group_rank
        }
    
    # 6. 准备返回结果
    return success({
        "student_id": current_student.id,
        "student_name": current_student.user.name,
        "class_id": student_class.id,
        "class_name": student_class.name,
        "personal_points": student_points,
        "class_rank": class_rank,
        "group_info": group_info
    })

# 辅助函数：计算小组的总积分
def calculate_group_total_points(class_id, group_id):
    # 获取小组内所有学生
    group_students = Student.query.filter_by(
        class_id=class_id,
        group_id=group_id
    ).all()
    
    # 计算所有学生积分总和
    return sum(s.performance_points for s in group_students)

# 辅助函数：计算学生在班级中的排名（处理并列情况）
def calculate_student_class_rank(class_id, student_points):
    # 获取班级内所有学生的积分，按降序排列
    class_students = Student.query.filter_by(class_id=class_id).all()
    scores = sorted([s.performance_points for s in class_students], reverse=True)
    
    if not scores:
        return 0
    
    # 处理并列排名逻辑
    rank = 1
    result_rank = 1
    
    for i in range(len(scores)):
        # 第一个元素直接是第1名
        if i == 0:
            if scores[i] == student_points:
                result_rank = rank
            continue
        
        # 当前分数与前一个不同时，更新排名为当前索引+1
        if scores[i] != scores[i-1]:
            rank = i + 1
        
        # 找到当前学生的分数，记录排名
        if scores[i] == student_points:
            result_rank = rank
    
    return result_rank

# 辅助函数：计算小组在班级中的排名（处理并列情况）
def calculate_group_class_rank(class_id, group_total):
    # 获取班级内所有小组及其总积分
    groups = StudyGroup.query.filter_by(class_id=class_id).all()
    group_totals = []
    
    for group in groups:
        total = calculate_group_total_points(class_id, group.id)
        group_totals.append(total)
    
    # 按积分降序排列
    sorted_totals = sorted(group_totals, reverse=True)
    
    if not sorted_totals:
        return 0
    
    # 处理并列排名逻辑
    rank = 1
    result_rank = 1
    
    for i in range(len(sorted_totals)):
        # 第一个元素直接是第1名
        if i == 0:
            if sorted_totals[i] == group_total:
                result_rank = rank
            continue
        
        # 当前积分与前一个不同时，更新排名为当前索引+1
        if sorted_totals[i] != sorted_totals[i-1]:
            rank = i + 1
        
        # 找到当前小组的积分，记录排名
        if sorted_totals[i] == group_total:
            result_rank = rank
    
    return result_rank

@point_bp.route('/reasons', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
def get_points_reasons():
    """获取加分扣分预设理由列表"""
    # 查询所有预设理由
    reasons = PointsReason.query.order_by(PointsReason.id).all()
    
    # 转换为字典列表返回
    reason_list = [reason.to_dict() for reason in reasons]
    
    return success({
        "total": len(reason_list),
        "reasons": reason_list
    })

@point_bp.route('/records', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher', 'student'])
@validate_request(QueryPointsRecordsRequest)
def get_points_records():
    """查询积分记录（支持多条件筛选和分页）"""
    params = g.request_data
    
    # 1. 基础查询
    query = db.session.query(
        PointsRecord,
        Student,
        Class,
        Teacher,
        User
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).join(
        Class, Student.class_id == Class.id
    ).join(
        Teacher, PointsRecord.teacher_id == Teacher.id
    ).join(
        User, Student.user_id == User.id, isouter=True
    )
    
    # 2. 根据角色添加过滤条件
    if g.role == 'student':
        # 学生只能查询自己的记录，忽略其他筛选参数
        current_student = Student.query.filter_by(user_id=g.user_id).first()
        if not current_student:
            return error(ErrorCode.NOT_FOUND, "学生信息不存在")
        query = query.filter(PointsRecord.student_id == current_student.id)
    else:
        # 管理员和教师可以使用所有筛选参数
        # 班级筛选
        if params.class_id is not None:
            query = query.filter(Student.class_id == params.class_id)
        
        # 学生姓名筛选（部分匹配）
        if params.student_name:
            query = query.filter(
                User.name.like(f"%{params.student_name}%")
            )
        
        # 学号筛选
        if params.student_number:
            query = query.filter(Student.student_id == params.student_number)
        
        # 教师权限额外限制：只能查看自己负责的班级记录（班主任 + 任课老师）
        if g.role == 'teacher':
            current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
            if current_teacher:
                # 1. 获取作为班主任负责的班级
                head_teacher_classes = Class.query.filter_by(
                    head_teacher_id=current_teacher.id
                ).with_entities(Class.id).all()
                head_teacher_class_ids = [cls.id for cls in head_teacher_classes]
                
                # 2. 获取作为任课老师负责的班级
                subject_teacher_classes = db.session.query(ClassSubjectTeacher.class_id).filter(
                    ClassSubjectTeacher.teacher_id == current_teacher.id
                ).distinct().all()
                subject_teacher_class_ids = [item[0] for item in subject_teacher_classes]
                
                # 3. 合并两类班级ID（去重）
                all_responsible_class_ids = list(set(head_teacher_class_ids + subject_teacher_class_ids))
                
                if not all_responsible_class_ids:
                    return error(ErrorCode.FORBIDDEN, "你不负责任何班级，无法查询积分记录")
                
                # 4. 按班级ID筛选
                query = query.filter(Student.class_id.in_(all_responsible_class_ids))
    
    # 3. 时间范围筛选（所有角色都适用）
    if params.start_time:
        query = query.filter(PointsRecord.created_at >= params.start_time)
    if params.end_time:
        query = query.filter(PointsRecord.created_at <= params.end_time)
    
    # 4. 计算总记录数（用于分页）
    total = query.count()
    
    # 5. 应用分页和排序
    records = query.order_by(PointsRecord.created_at.desc()) \
                    .offset(params.start) \
                    .limit(params.limit) \
                    .all()
    
    # 6. 格式化返回结果
    result = []
    for record, student, cls, teacher, student_user in records:
        # 获取教师用户信息（姓名）
        teacher_user = User.query.get(teacher.user_id)
        
        result.append({
            "student_id": student.id,
            "student_name": student_user.name if student_user else None,
            "points_change": record.points_change,
            "class_name": cls.name,
            "class_grade": cls.grade,
            "teacher_id": teacher.id,
            "teacher_name": teacher_user.name if teacher_user else None,
            "operated_at": record.created_at.strftime("%Y-%m-%d %H:%M:%S"),
            "reason": record.reason
        })
    
    return success({
        "records": result,
        "total": total,
        "start": params.start,
        "limit": params.limit
    })

@point_bp.route('/statistics', methods=['POST'])
@auth_required
@validate_request(PointsStatisticsRequest)
def get_points_statistics():
    """统计加扣分信息（支持班级、小组、学生多维度）"""
    params = g.request_data
    
    # 1. 处理学生权限：只能查看自己的统计信息
    current_user_id = g.user_id
    target_student_id = None
    if g.role == 'student':
        student = Student.query.filter_by(user_id=current_user_id).first()
        if not student:
            return error(ErrorCode.NOT_FOUND, "学生信息不存在")
        target_student_id = student.id
        # 忽略传入的所有筛选参数，强制查询当前学生
        params.class_id = None
        params.group_id = None
        params.student_id = target_student_id
        # 自动填充班级和小组ID
        if not params.class_id:
            params.class_id = student.class_id
        if not params.group_id and student.group_id:
            params.group_id = student.group_id
    
    # 2. 验证参数合法性（admin和teacher）
    if g.role in ['admin', 'teacher']:
        # 确保至少提供一个筛选条件
        if not any([params.class_id, params.group_id, params.student_id]):
            return error(ErrorCode.INVALID_PARAM, "必须提供class_id、group_id或student_id中的一个")
        
        # 如果提供了小组ID，验证小组存在且属于指定班级
        if params.group_id:
            group = StudyGroup.query.get(params.group_id)
            if not group:
                return error(ErrorCode.NOT_FOUND, f"小组ID {params.group_id} 不存在")
            # 如果同时提供了班级ID，验证匹配
            if params.class_id and group.class_id != params.class_id:
                return error(ErrorCode.INVALID_PARAM, "小组不属于指定班级")
            # 自动填充班级ID
            if not params.class_id:
                params.class_id = group.class_id
        
        # 如果提供了学生ID，验证学生存在且属于指定班级/小组
        if params.student_id:
            student = Student.query.get(params.student_id)
            if not student:
                return error(ErrorCode.NOT_FOUND, f"学生ID {params.student_id} 不存在")
            # 验证班级归属
            if params.class_id and student.class_id != params.class_id:
                return error(ErrorCode.INVALID_PARAM, "学生不属于指定班级")
            # 验证小组归属
            if params.group_id and student.group_id != params.group_id:
                return error(ErrorCode.INVALID_PARAM, "学生不属于指定小组")
            # 自动填充班级和小组ID
            if not params.class_id:
                params.class_id = student.class_id
            if not params.group_id and student.group_id:
                params.group_id = student.group_id
    
    # 3. 教师权限过滤：只能查看自己负责的班级
    if g.role == 'teacher':
        current_teacher = Teacher.query.filter_by(user_id=current_user_id).first()
        if not current_teacher:
            return error(ErrorCode.FORBIDDEN, "教师信息不存在")
        
        # 获取教师负责的所有班级
        head_teacher_classes = Class.query.filter_by(head_teacher_id=current_teacher.id).with_entities(Class.id).all()
        subject_teacher_classes = db.session.query(ClassSubjectTeacher.class_id).filter(
            ClassSubjectTeacher.teacher_id == current_teacher.id
        ).distinct().all()
        
        responsible_class_ids = list(set(
            [cls.id for cls in head_teacher_classes] + 
            [item[0] for item in subject_teacher_classes]
        ))
        
        # 验证访问权限
        if params.class_id not in responsible_class_ids:
            return error(ErrorCode.FORBIDDEN, "你不负责该班级，无法查看统计信息")
    
    # 4. 获取预设理由列表
    preset_reasons = {r.reason: r.id for r in PointsReason.query.all()}
    
    # 5. 根据不同维度进行统计
    if params.class_id and not params.group_id and not (params.student_id or target_student_id):
        # 维度1：班级统计
        return _statistics_by_class(params, preset_reasons)
        
    elif params.group_id and not (params.student_id or target_student_id):
        # 维度2：小组统计
        return _statistics_by_group(params, preset_reasons)
        
    else:
        # 维度3：学生统计
        student_id = target_student_id or params.student_id
        return _statistics_by_student(params, preset_reasons, student_id)

def _statistics_by_class(params, preset_reasons):
    """按班级统计加扣分信息（修复版）"""
    class_id = params.class_id
    
    # 1. 定义加分和扣分的计算表达式
    add_case = case(
        (PointsRecord.points_change > 0, PointsRecord.points_change),
        else_=0
    ).label('add_points')
    
    minus_case = case(
        (PointsRecord.points_change < 0, PointsRecord.points_change),
        else_=0
    ).label('minus_points')
    
    # 2. 计算总加分和总扣分
    query = db.session.query(
        func.sum(add_case).label('total_add'),
        func.sum(minus_case).label('total_minus')
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).filter(Student.class_id == class_id)
    
    # 应用时间筛选
    if params.start_time:
        query = query.filter(PointsRecord.created_at >= params.start_time)
    if params.end_time:
        query = query.filter(PointsRecord.created_at <= params.end_time)
    
    total_stats = query.first()
    total_add = total_stats.total_add or 0
    total_minus = total_stats.total_minus or 0
    
    # 3. 统计各预设理由的加分占比（转换为列表格式）
    add_reasons = []
    if total_add > 0:
        reason_dict = {}
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).join(
                Student, PointsRecord.student_id == Student.id
            ).filter(
                Student.class_id == class_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change > 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum > 0:
                percentage = round((reason_sum / total_add) * 100, 2)
                reason_dict[reason_name] = {
                    'points': reason_sum,
                    'percentage': percentage
                }
        
        # 计算"其他"理由的加分
        other_add_sum = total_add - sum(v['points'] for v in reason_dict.values())
        if other_add_sum > 0:
            reason_dict['其他'] = {
                'points': other_add_sum,
                'percentage': round((other_add_sum / total_add) * 100, 2)
            }
        
        # 转换为列表格式
        add_reasons = [
            {'reason': k, 'points': v['points'], 'percentage': v['percentage']}
            for k, v in reason_dict.items()
        ]
    
    # 4. 统计各预设理由的扣分占比（转换为列表格式）
    minus_reasons = []
    if total_minus < 0:  # 扣分总和为负数
        total_minus_abs = abs(total_minus)  # 取绝对值用于计算百分比
        reason_dict = {}
        
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).join(
                Student, PointsRecord.student_id == Student.id
            ).filter(
                Student.class_id == class_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change < 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum < 0:  # 存在扣分项
                reason_sum_abs = abs(reason_sum)
                percentage = round((reason_sum_abs / total_minus_abs) * 100, 2)
                reason_dict[reason_name] = {
                    'points': reason_sum,  # 保持负数
                    'percentage': percentage
                }
        
        # 计算"其他"理由的扣分
        other_minus_sum = total_minus - sum(v['points'] for v in reason_dict.values())
        if other_minus_sum < 0:
            reason_dict['其他'] = {
                'points': other_minus_sum,
                'percentage': round((abs(other_minus_sum) / total_minus_abs) * 100, 2)
            }
        
        # 转换为列表格式
        minus_reasons = [
            {'reason': k, 'points': v['points'], 'percentage': v['percentage']}
            for k, v in reason_dict.items()
        ]
    
    # 获取班级信息
    cls = Class.query.get(class_id)
    
    return success({
        'type': 'class',
        'class_id': class_id,
        'class_name': cls.name if cls else None,
        'grade': cls.grade if cls else None,
        'total_add': total_add,
        'total_minus': total_minus,
        'add_reasons': add_reasons,
        'minus_reasons': minus_reasons
    })

def _statistics_by_group(params, preset_reasons):
    """按小组统计加扣分信息（修复版）"""
    group_id = params.group_id
    class_id = params.class_id
    
    # 1. 定义加分和扣分的计算表达式
    add_case = case(
        (PointsRecord.points_change > 0, PointsRecord.points_change),
        else_=0
    ).label('add_points')
    
    minus_case = case(
        (PointsRecord.points_change < 0, PointsRecord.points_change),
        else_=0
    ).label('minus_points')
    
    # 2. 计算总加分和总扣分
    query = db.session.query(
        func.sum(add_case).label('total_add'),
        func.sum(minus_case).label('total_minus')
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).filter(
        Student.class_id == class_id,
        Student.group_id == group_id
    )
    
    # 应用时间筛选
    if params.start_time:
        query = query.filter(PointsRecord.created_at >= params.start_time)
    if params.end_time:
        query = query.filter(PointsRecord.created_at <= params.end_time)
    
    total_stats = query.first()
    total_add = total_stats.total_add or 0
    total_minus = total_stats.total_minus or 0
    
    # 3. 统计各预设理由的加分占比（转换为列表格式）
    add_reasons = []
    if total_add > 0:
        reason_dict = {}
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).join(
                Student, PointsRecord.student_id == Student.id
            ).filter(
                Student.class_id == class_id,
                Student.group_id == group_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change > 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum > 0:
                percentage = round((reason_sum / total_add) * 100, 2)
                reason_dict[reason_name] = {
                    'points': reason_sum,
                    'percentage': percentage
                }
        
        # 计算"其他"理由的加分
        other_add_sum = total_add - sum(v['points'] for v in reason_dict.values())
        if other_add_sum > 0:
            reason_dict['其他'] = {
                'points': other_add_sum,
                'percentage': round((other_add_sum / total_add) * 100, 2)
            }
        
        # 转换为列表格式
        add_reasons = [
            {'reason': k, 'points': v['points'], 'percentage': v['percentage']}
            for k, v in reason_dict.items()
        ]
    
    # 4. 统计各预设理由的扣分占比（转换为列表格式）
    minus_reasons = []
    if total_minus < 0:
        total_minus_abs = abs(total_minus)
        reason_dict = {}
        
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).join(
                Student, PointsRecord.student_id == Student.id
            ).filter(
                Student.class_id == class_id,
                Student.group_id == group_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change < 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum < 0:
                reason_sum_abs = abs(reason_sum)
                percentage = round((reason_sum_abs / total_minus_abs) * 100, 2)
                reason_dict[reason_name] = {
                    'points': reason_sum,
                    'percentage': percentage
                }
        
        # 计算"其他"理由的扣分
        other_minus_sum = total_minus - sum(v['points'] for v in reason_dict.values())
        if other_minus_sum < 0:
            reason_dict['其他'] = {
                'points': other_minus_sum,
                'percentage': round((abs(other_minus_sum) / total_minus_abs) * 100, 2)
            }
        
        # 转换为列表格式
        minus_reasons = [
            {'reason': k, 'points': v['points'], 'percentage': v['percentage']}
            for k, v in reason_dict.items()
        ]
    
    # 获取小组和班级信息
    group = StudyGroup.query.get(group_id)
    cls = Class.query.get(class_id)
    
    return success({
        'type': 'group',
        'class_id': class_id,
        'class_name': cls.name if cls else None,
        'grade': cls.grade if cls else None,
        'group_id': group_id,
        'group_name': group.name if group else None,
        'total_add': total_add,
        'total_minus': total_minus,
        'add_reasons': add_reasons,
        'minus_reasons': minus_reasons
    })

def _statistics_by_student(params, preset_reasons, student_id):
    """按学生统计加扣分信息（修复版）"""
    class_id = params.class_id
    group_id = params.group_id
    
    # 1. 定义加分和扣分的计算表达式
    add_case = case(
        (PointsRecord.points_change > 0, PointsRecord.points_change),
        else_=0
    ).label('add_points')
    
    minus_case = case(
        (PointsRecord.points_change < 0, PointsRecord.points_change),
        else_=0
    ).label('minus_points')
    
    # 2. 计算学生的总加分和总扣分
    student_query = db.session.query(
        func.sum(add_case).label('total_add'),
        func.sum(minus_case).label('total_minus')
    ).filter(PointsRecord.student_id == student_id)
    
    # 应用时间筛选
    if params.start_time:
        student_query = student_query.filter(PointsRecord.created_at >= params.start_time)
    if params.end_time:
        student_query = student_query.filter(PointsRecord.created_at <= params.end_time)
    
    student_stats = student_query.first()
    student_add = student_stats.total_add or 0
    student_minus = student_stats.total_minus or 0
    
    # 3. 计算班级总加分和总扣分（用于计算占比）
    class_add = 0
    class_minus = 0
    if class_id:
        class_query = db.session.query(
            func.sum(add_case).label('total_add'),
            func.sum(minus_case).label('total_minus')
        ).join(
            Student, PointsRecord.student_id == Student.id
        ).filter(Student.class_id == class_id)
        
        # 应用时间筛选
        if params.start_time:
            class_query = class_query.filter(PointsRecord.created_at >= params.start_time)
        if params.end_time:
            class_query = class_query.filter(PointsRecord.created_at <= params.end_time)
        
        class_stats = class_query.first()
        class_add = class_stats.total_add or 0
        class_minus = class_stats.total_minus or 0
    
    # 4. 计算小组总加分和总扣分（用于计算占比）
    group_add = 0
    group_minus = 0
    if group_id:
        group_query = db.session.query(
            func.sum(add_case).label('total_add'),
            func.sum(minus_case).label('total_minus')
        ).join(
            Student, PointsRecord.student_id == Student.id
        ).filter(
            Student.class_id == class_id,
            Student.group_id == group_id
        )
        
        # 应用时间筛选
        if params.start_time:
            group_query = group_query.filter(PointsRecord.created_at >= params.start_time)
        if params.end_time:
            group_query = group_query.filter(PointsRecord.created_at <= params.end_time)
        
        group_stats = group_query.first()
        group_add = group_stats.total_add or 0
        group_minus = group_stats.total_minus or 0
    
    # 5. 统计学生各加分理由（转换为列表格式）
    add_reasons = []
    if student_add > 0:
        reason_dict = {}
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).filter(
                PointsRecord.student_id == student_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change > 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum > 0:
                # 计算占个人总加分的比例
                personal_percentage = round((reason_sum / student_add) * 100, 2)
                
                # 计算占班级总加分的比例（如果班级数据存在）
                class_percentage = round((reason_sum / class_add) * 100, 2) if class_add > 0 else 0
                
                # 计算占小组总加分的比例（如果小组数据存在）
                group_percentage = round((reason_sum / group_add) * 100, 2) if group_add > 0 else 0
                
                reason_dict[reason_name] = {
                    'points': reason_sum,
                    'personal_percentage': personal_percentage,
                    'class_percentage': class_percentage,
                    'group_percentage': group_percentage
                }
        
        # 计算"其他"理由的加分
        other_add_sum = student_add - sum(v['points'] for v in reason_dict.values())
        if other_add_sum > 0:
            personal_percentage = round((other_add_sum / student_add) * 100, 2)
            class_percentage = round((other_add_sum / class_add) * 100, 2) if class_add > 0 else 0
            group_percentage = round((other_add_sum / group_add) * 100, 2) if group_add > 0 else 0
            
            reason_dict['其他'] = {
                'points': other_add_sum,
                'personal_percentage': personal_percentage,
                'class_percentage': class_percentage,
                'group_percentage': group_percentage
            }
        
        # 转换为列表格式
        add_reasons = [
            {
                'reason': k,
                'points': v['points'],
                'personal_percentage': v['personal_percentage'],
                'class_percentage': v['class_percentage'],
                'group_percentage': v['group_percentage']
            }
            for k, v in reason_dict.items()
        ]
    
    # 6. 统计学生各扣分理由（转换为列表格式）
    minus_reasons = []
    if student_minus < 0:
        student_minus_abs = abs(student_minus)
        reason_dict = {}
        
        for reason_name in preset_reasons:
            reason_query = db.session.query(
                func.sum(PointsRecord.points_change).label('reason_sum')
            ).filter(
                PointsRecord.student_id == student_id,
                PointsRecord.reason == reason_name,
                PointsRecord.points_change < 0
            )
            
            # 应用时间筛选
            if params.start_time:
                reason_query = reason_query.filter(PointsRecord.created_at >= params.start_time)
            if params.end_time:
                reason_query = reason_query.filter(PointsRecord.created_at <= params.end_time)
            
            reason_sum = reason_query.scalar() or 0
            
            if reason_sum < 0:
                reason_sum_abs = abs(reason_sum)
                # 计算占个人总扣分的比例
                personal_percentage = round((reason_sum_abs / student_minus_abs) * 100, 2)
                
                # 计算占班级总扣分的比例（如果班级数据存在）
                class_percentage = round((reason_sum_abs / abs(class_minus)) * 100, 2) if class_minus != 0 else 0
                
                # 计算占小组总扣分的比例（如果小组数据存在）
                group_percentage = round((reason_sum_abs / abs(group_minus)) * 100, 2) if group_minus != 0 else 0
                
                reason_dict[reason_name] = {
                    'points': reason_sum,
                    'personal_percentage': personal_percentage,
                    'class_percentage': class_percentage,
                    'group_percentage': group_percentage
                }
        
        # 计算"其他"理由的扣分
        other_minus_sum = student_minus - sum(v['points'] for v in reason_dict.values())
        if other_minus_sum < 0:
            other_minus_abs = abs(other_minus_sum)
            personal_percentage = round((other_minus_abs / student_minus_abs) * 100, 2)
            class_percentage = round((other_minus_abs / abs(class_minus)) * 100, 2) if class_minus != 0 else 0
            group_percentage = round((other_minus_abs / abs(group_minus)) * 100, 2) if group_minus != 0 else 0
            
            reason_dict['其他'] = {
                'points': other_minus_sum,
                'personal_percentage': personal_percentage,
                'class_percentage': class_percentage,
                'group_percentage': group_percentage
            }
        
        # 转换为列表格式
        minus_reasons = [
            {
                'reason': k,
                'points': v['points'],
                'personal_percentage': v['personal_percentage'],
                'class_percentage': v['class_percentage'],
                'group_percentage': v['group_percentage']
            }
            for k, v in reason_dict.items()
        ]
    
    # 获取学生、班级和小组信息
    student = Student.query.get(student_id)
    student_user = User.query.get(student.user_id) if student else None
    cls = Class.query.get(class_id) if class_id else None
    group = StudyGroup.query.get(group_id) if group_id else None
    
    return success({
        'type': 'student',
        'student_id': student_id,
        'student_name': student_user.name if student_user else None,
        'class_id': class_id,
        'class_name': cls.name if cls else None,
        'grade': cls.grade if cls else None,
        'group_id': group_id,
        'group_name': group.name if group else None,
        'total_add': student_add,
        'total_minus': student_minus,
        'class_total_add': class_add,
        'class_total_minus': class_minus,
        'group_total_add': group_add,
        'group_total_minus': group_minus,
        'add_reasons': add_reasons,
        'minus_reasons': minus_reasons
    })

@point_bp.route('/export', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(PointsStatisticsRequest)
def export_points_records():
    """导出加扣分情况到Excel表格"""
    params = g.request_data
    
    # 1. 验证参数合法性
    if g.role == 'teacher' and (not any([params.class_id, params.group_id, params.student_id])):
        return error(ErrorCode.INVALID_PARAM, "必须提供class_id、group_id或student_id中的一个")
    
    # 2. 验证班级、小组、学生的关联性
    if params.group_id:
        group = StudyGroup.query.get(params.group_id)
        if not group:
            return error(ErrorCode.NOT_FOUND, f"小组ID {params.group_id} 不存在")
        if params.class_id and group.class_id != params.class_id:
            return error(ErrorCode.INVALID_PARAM, "小组不属于指定班级")
        if not params.class_id:
            params.class_id = group.class_id
    
    if params.student_id:
        student = Student.query.get(params.student_id)
        if not student:
            return error(ErrorCode.NOT_FOUND, f"学生ID {params.student_id} 不存在")
        if params.class_id and student.class_id != params.class_id:
            return error(ErrorCode.INVALID_PARAM, "学生不属于指定班级")
        if params.group_id and student.group_id != params.group_id:
            return error(ErrorCode.INVALID_PARAM, "学生不属于指定小组")
        if not params.class_id:
            params.class_id = student.class_id
        if not params.group_id and student.group_id:
            params.group_id = student.group_id
    
    # 3. 教师权限过滤
    if g.role == 'teacher':
        current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not current_teacher:
            return error(ErrorCode.FORBIDDEN, "教师信息不存在")
        
        # 获取教师负责的所有班级
        head_teacher_classes = Class.query.filter_by(head_teacher_id=current_teacher.id).with_entities(Class.id).all()
        subject_teacher_classes = db.session.query(ClassSubjectTeacher.class_id).filter(
            ClassSubjectTeacher.teacher_id == current_teacher.id
        ).distinct().all()
        
        responsible_class_ids = list(set(
            [cls.id for cls in head_teacher_classes] + 
            [item[0] for item in subject_teacher_classes]
        ))
        
        if params.class_id not in responsible_class_ids:
            return error(ErrorCode.FORBIDDEN, "你不负责该班级，无法导出记录")
    
    # 4. 构建查询
    teacher_user_alias = alias(User, name='teacher_user') # 为教师用户表创建别名
    student_user_alias = alias(User, name='student_user') # 为学生用户表创建别名
    query = db.session.query(
        PointsRecord,
        Student,
        Class,
        Teacher,
        teacher_user_alias.c.name,
        student_user_alias.c.name
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).join(
        Class, Student.class_id == Class.id
    ).join(
        Teacher, PointsRecord.teacher_id == Teacher.id
    ).join(
        teacher_user_alias, Teacher.user_id == teacher_user_alias.c.id, isouter=True
    ).join(
        student_user_alias, Student.user_id == student_user_alias.c.id, isouter=True
    )
    
    # 5. 应用筛选条件
    if params.start_time:
        query = query.filter(PointsRecord.created_at >= params.start_time)
    if params.end_time:
        query = query.filter(PointsRecord.created_at <= params.end_time)
    if params.class_id:
        query = query.filter(Student.class_id == params.class_id)
    if params.group_id:
        query = query.filter(Student.group_id == params.group_id)
    if params.student_id:
        query = query.filter(PointsRecord.student_id == params.student_id)
    
    # 按时间降序排序
    query = query.order_by(PointsRecord.created_at.desc())
    
    # 执行查询
    records = query.all()
    
    if not records:
        return error(ErrorCode.NOT_FOUND, "没有符合条件的记录")
    
    # 6. 创建Excel文件
    output = BytesIO()
    workbook = xlsxwriter.Workbook(output)
    
    # 创建工作表
    worksheet = workbook.add_worksheet('加扣分记录')
    
    # 定义表头样式
    header_style = workbook.add_format({
        'bold': True,
        'bg_color': '#F0F0F0',
        'border': 1,
        'align': 'center',
        'valign': 'vcenter'
    })
    
    # 定义内容样式
    content_style = workbook.add_format({
        'border': 1,
        'align': 'left',
        'valign': 'vcenter'
    })
    
    # 加分样式（绿色）
    add_style = workbook.add_format({
        'border': 1,
        'align': 'left',
        'valign': 'vcenter',
        'font_color': 'green'
    })
    
    # 扣分样式（红色）
    minus_style = workbook.add_format({
        'border': 1,
        'align': 'left',
        'valign': 'vcenter',
        'font_color': 'red'
    })
    
    # 写入表头
    headers = [
        '学生姓名', '所在班级名称', '所在年级', 
        '加扣分值', '加扣分原因', '操作老师姓名', '操作时间'
    ]
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_style)
    
    # 设置列宽
    worksheet.set_column(0, 0, 15)  # 学生姓名
    worksheet.set_column(1, 1, 20)  # 班级名称
    worksheet.set_column(2, 2, 10)  # 年级
    worksheet.set_column(3, 3, 10)  # 加扣分值
    worksheet.set_column(4, 4, 30)  # 加扣分原因
    worksheet.set_column(5, 5, 15)  # 操作老师
    worksheet.set_column(6, 6, 20)  # 操作时间
    
    # 写入数据
    for row, (record, student, cls, teacher, teacher_name, student_name) in enumerate(records, start=1):
        # 学生姓名

        worksheet.write(row, 0, student_name if student_name else '', content_style)
        
        # 班级名称
        worksheet.write(row, 1, cls.name if cls else '', content_style)
        
        # 年级
        worksheet.write(row, 2, cls.grade if cls else '', content_style)
        
        # 加扣分值（根据正负值使用不同样式）
        points = record.points_change
        if points > 0:
            worksheet.write(row, 3, f"+{points}", add_style)
        else:
            worksheet.write(row, 3, points, minus_style)
        
        # 加扣分原因
        worksheet.write(row, 4, record.reason or '', content_style)
        
        # 操作老师姓名
        worksheet.write(row, 5, teacher_name if teacher_name else '', content_style)
        
        # 操作时间
        if record.created_at:
            worksheet.write(row, 6, record.created_at.strftime("%Y-%m-%d %H:%M:%S"), content_style)
        else:
            worksheet.write(row, 6, '', content_style)
    
    # 关闭工作簿
    workbook.close()
    
    # 7. 准备响应
    output.seek(0)
    response = make_response(output.getvalue())
    
    # 设置文件名，包含当前日期和筛选条件
    date_str = datetime.now().strftime("%Y%m%d")
    filename = f"加扣分记录_{date_str}"
    
    if params.class_id:
        cls = Class.query.get(params.class_id)
        if cls:
            filename += f"_{cls.name}"
    elif params.group_id:
        group = StudyGroup.query.get(params.group_id)
        if group:
            filename += f"_小组{group.name}"
    elif params.student_id:
        student = Student.query.get(params.student_id)
        if student_name:
            filename += f"_{student_name}"
    
    filename += ".xlsx"
    encoded_filename = urllib.parse.quote(filename)
    
    # 设置响应头
    response.headers["Content-Disposition"] = f"attachment; filename*=UTF-8''{encoded_filename}"
    response.headers["Content-type"] = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    
    return response

@point_bp.route('/report', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(PointsReportRequest)
def generate_points_report():
    """生成积分报表，包含班级、小组和学生的积分统计及排名"""
    params = g.request_data
    
    # 1. 验证参数
    if not params.class_id:
        return error(ErrorCode.INVALID_PARAM, "必须提供班级ID")
        
    if not (params.start_time and params.end_time):
        return error(ErrorCode.INVALID_PARAM, "必须提供完整的时间范围")
        
    if params.start_time > params.end_time:
        return error(ErrorCode.INVALID_PARAM, "开始时间不能晚于结束时间")
    
    # 2. 验证班级存在
    cls = Class.query.get(params.class_id)
    if not cls:
        return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 不存在")
    
    # 3. 教师权限过滤
    if g.role == 'teacher':
        current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not current_teacher:
            return error(ErrorCode.FORBIDDEN, "教师信息不存在")
        
        # 获取教师负责的所有班级
        head_teacher_classes = Class.query.filter_by(head_teacher_id=current_teacher.id).with_entities(Class.id).all()
        subject_teacher_classes = db.session.query(ClassSubjectTeacher.class_id).filter(
            ClassSubjectTeacher.teacher_id == current_teacher.id
        ).distinct().all()
        
        responsible_class_ids = list(set(
            [cls.id for cls in head_teacher_classes] + 
            [item[0] for item in subject_teacher_classes]
        ))
        
        if params.class_id not in responsible_class_ids:
            return error(ErrorCode.FORBIDDEN, "你不负责该班级，无法生成报表")
    
    # 4. 基础查询条件
    time_filter = and_(
        PointsRecord.created_at >= params.start_time,
        PointsRecord.created_at <= params.end_time
    )
    class_filter = Student.class_id == params.class_id
    
    # 5. 班级总积分统计
    class_stats = db.session.query(
        func.sum(case((PointsRecord.points_change > 0, PointsRecord.points_change), else_=0)).label('total_add'),
        func.sum(case((PointsRecord.points_change < 0, PointsRecord.points_change), else_=0)).label('total_minus')
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).filter(
        time_filter,
        class_filter
    ).first()
    
    total_add = class_stats.total_add or 0
    total_minus = class_stats.total_minus or 0
    
    # 6. 各理由的积分统计（包含所有理由）
    reason_stats = db.session.query(
        PointsRecord.reason,
        func.sum(case((PointsRecord.points_change > 0, PointsRecord.points_change), else_=0)).label('add_points'),
        func.sum(case((PointsRecord.points_change < 0, PointsRecord.points_change), else_=0)).label('minus_points')
    ).join(
        Student, PointsRecord.student_id == Student.id
    ).filter(
        time_filter,
        class_filter
    ).group_by(
        PointsRecord.reason
    ).all()
    
    reasons = []
    for reason, add, minus in reason_stats:
        reasons.append({
            'reason': reason or '无理由',
            'add_points': add or 0,
            'minus_points': minus or 0
        })
    
    # 7. 小组积分统计及排名
    # 获取班级所有小组
    groups = StudyGroup.query.filter_by(class_id=params.class_id).all()
    group_stats = []
    
    for group in groups:
        # 计算小组总积分
        group_points = db.session.query(
            func.sum(case((PointsRecord.points_change > 0, PointsRecord.points_change), else_=0)).label('add'),
            func.sum(case((PointsRecord.points_change < 0, PointsRecord.points_change), else_=0)).label('minus')
        ).join(
            Student, PointsRecord.student_id == Student.id
        ).filter(
            time_filter,
            Student.group_id == group.id,
            class_filter
        ).first()
        
        group_stats.append({
            'group_id': group.id,
            'group_name': group.name,
            'total_add': group_points.add or 0,
            'total_minus': group_points.minus or 0,
            'total': (group_points.add or 0) + (group_points.minus or 0)
        })
    
    # 小组排名（处理并列情况）
    sorted_groups = sorted(group_stats, key=lambda x: x['total'], reverse=True)
    group_ranking = []
    if sorted_groups:
        current_rank = 1
        prev_total = sorted_groups[0]['total']
        
        for i, group in enumerate(sorted_groups):
            if i > 0 and group['total'] != prev_total:
                current_rank = i + 1
                prev_total = group['total']
            
            group_ranking.append({
                **group,
                'rank': current_rank
            })
    
    # 8. 学生积分统计及排名
    # 获取班级所有学生
    students = Student.query.filter_by(class_id=params.class_id).all()
    student_stats = []
    
    for student in students:
        # 计算学生总积分
        student_points = db.session.query(
            func.sum(case((PointsRecord.points_change > 0, PointsRecord.points_change), else_=0)).label('add'),
            func.sum(case((PointsRecord.points_change < 0, PointsRecord.points_change), else_=0)).label('minus')
        ).filter(
            time_filter,
            PointsRecord.student_id == student.id
        ).first()
        
        # 获取学生姓名
        student_user = User.query.get(student.user_id)
        student_name = student_user.name if student_user else ''
        
        # 获取学生所在小组
        group_name = ''
        if student.group_id:
            group = StudyGroup.query.get(student.group_id)
            group_name = group.name if group else ''
        
        student_stats.append({
            'student_id': student.id,
            'student_name': student_name,
            'group_id': student.group_id,
            'group_name': group_name,
            'total_add': student_points.add or 0,
            'total_minus': student_points.minus or 0,
            'total': (student_points.add or 0) + (student_points.minus or 0)
        })
    
    # 学生排名（处理并列情况）
    sorted_students = sorted(student_stats, key=lambda x: x['total'], reverse=True)
    student_ranking = []
    if sorted_students:
        current_rank = 1
        prev_total = sorted_students[0]['total']
        
        for i, student in enumerate(sorted_students):
            if i > 0 and student['total'] != prev_total:
                current_rank = i + 1
                prev_total = student['total']
            
            student_ranking.append({
                **student,
                'rank': current_rank
            })
    
    # 9. 整理返回结果
    return success({
        'class_info': {
            'class_id': cls.id,
            'class_name': cls.name,
            'grade': cls.grade,
            'time_range': {
                'start_time': params.start_time.strftime("%Y-%m-%d %H:%M:%S"),
                'end_time': params.end_time.strftime("%Y-%m-%d %H:%M:%S")
            },
            'total_add': total_add,
            'total_minus': total_minus,
            'total': total_add + total_minus
        },
        'reason_stats': reasons,
        'group_ranking': group_ranking,
        'student_ranking': student_ranking
    })